﻿using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace CARD_INFO
{
    public abstract class DB_SQLServer
    {
        private static string connectionString = ConfigurationManager.AppSettings["数据库连接字符串"];


        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, string cmdText)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                cmd.CommandTimeout = 5;
            }
            catch
            {
                throw;
            }
        }

        public static int ExecuteNonQuery(string cmdText)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    PrepareCommand(cmd, conn, cmdText);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }

        public static SqlDataReader ExecuteReader(string cmdText)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                SqlConnection conn = new SqlConnection(connectionString);

                try
                {
                    PrepareCommand(cmd, conn, cmdText);
                    SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    cmd.Parameters.Clear();
                    return rdr;
                }
                catch
                {
                    conn.Close();
                    throw;
                }
            }
        }

        public static object ExecuteScalar(string cmdText)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    PrepareCommand(cmd, conn, cmdText);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }

        public static DataTable ExecuteDataTable(string cmdText)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    PrepareCommand(cmd, conn, cmdText);

                    using (SqlDataAdapter thisAdapter = new SqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();

                        try
                        {
                            thisAdapter.Fill(dt);
                            thisAdapter.Dispose();
                        }
                        catch { }
                        finally
                        {
                            if (conn.State == ConnectionState.Open)
                            {
                                conn.Dispose();
                            }
                        }

                        return dt;
                    }
                }
            }
        }
    }
}
